/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Cocktail;
import model.Recipe;

/**
 *
 * @author tamara
 */
public class RecipeTable {

    private DatabaseConnection db = new DatabaseConnection();
    private ArrayList<Recipe> recipe = new ArrayList<Recipe>();
    private Connection connection;
    private Statement statement;
    private ResultSet rs;

    public void establishConnection() {
        db.connect();
    }

    public void cancelConnection() {
        db.disconnect();
    }

    public boolean isAvailableCocktail(Cocktail cocktail) {
        boolean isAvailable = false;
        try {
            this.establishConnection();
            connection = db.getCon();
            statement = connection.createStatement();
            statement.execute("SELECT Verfuegbarkeit "
                    + "FROM (Rezept r "
                    + "INNER JOIN Cocktail c ON (r.CocktailID = c.CocktailID)) "
                    + "INNER JOIN Zutat z ON (r.ZutatID = z.ZutatID) "
                    + "WHERE r.CocktailID=" + cocktail.getCocktailID() + ";");
            rs = statement.getResultSet();
            if (rs != null) {

                while (rs.next()) {
                    if (rs.getBoolean(1) == true) {
                        isAvailable = true;
                    } else {
                        isAvailable = false;
                        break;
                    }
                }
            }
            this.cancelConnection();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return isAvailable;
    }

    public void newRecipe(Cocktail cocktail, Recipe recipe) {
        try {
            this.establishConnection();
            connection = db.getCon();
            statement = connection.createStatement();
            statement.executeUpdate("INSERT INTO Rezept VALUES (" + cocktail.getCocktailID() + "," + recipe.getIngredientID() + "," + recipe.getMenge() + ",'" + recipe.getUnit() + "')");
            connection.commit();
            this.cancelConnection();

        } catch (SQLException ex) {
            Logger.getLogger(RecipeTable.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<Recipe> getRecipe(int cocktailid) {
        try {
            this.establishConnection();
            connection = db.getCon();
            statement = connection.createStatement();

            statement.execute("SELECT r.cocktailid, r.zutatid, r.menge, r.einheit, c.cocktailname, z.zutatname "
                    + "FROM (Rezept r "
                    + "INNER JOIN Cocktail c ON (r.CocktailID = c.CocktailID)) "
                    + "INNER JOIN Zutat z ON (r.ZutatID = z.ZutatID) "
                    + "WHERE r.CocktailID=" + cocktailid + ";");

            rs = statement.getResultSet();

            Recipe r_recipe;

            if (rs != null) {
                while (rs.next()) {
                    r_recipe = new Recipe(rs.getInt(1), rs.getInt(2), rs.getInt(3), rs.getString(4), rs.getString(5), rs.getString(6));
                    recipe.add(r_recipe);
                    System.out.println("Recipe: " + r_recipe);
                }
            }

            this.cancelConnection();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeTable.class.getName()).log(Level.SEVERE, null, ex);
        }
        return recipe;
    }
}